ALTER PROCEDURE SP_CREATE_UPDATE_PO_DETAILS
(
@PO_NO			VARCHAR(20),
@VENDOR_NAME	VARCHAR(100),
@PO_LINEITEM_NO VARCHAR(10),
@PO_TYPE		INT,
@PO_DATE		DATETIME,
@PART_NUMBER    VARCHAR(50),
@REQ_QTY		INT,
@STATUS			VARCHAr(50),
@REQ_DATE		DATETIME,
@PS_TYPE        VARCHAR(50),
@PO_UNIT_COST	NUMERIC(18,4)
)
AS
BEGIN
		DECLARE @VENDOR_ID INT
		DECLARE @PO_ID     INT
		DECLARE @PROCESSSTEP_ID INT
		DECLARE @PO_LINE_ITEM_ID INT
		DECLARE @shippingAddressId INT
		DECLARE @billingAddressId INT

		SELECT @PROCESSSTEP_ID = P.PROCESSSTEP_ID FROM PRODUCT P, PROCESS_STEP PS WHERE P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND PART_NUMBER = @PART_NUMBER AND PS_TYPE = @PS_TYPE
		IF @PROCESSSTEP_ID IS nULL
		BEGIN
				SELECT @PROCESSSTEP_ID = P.PROCESSSTEP_ID FROM PRODUCT_MAP_TABLE P, PROCESS_STEP PS WHERE P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND PART_NUMBER = @PART_NUMBER AND PS_TYPE = @PS_TYPE
				IF @PROCESSSTEP_ID IS nULL
				BEGIN
						 -- DUMMY UPDATE TO AVOID SQLEXCEPTION
 						UPDATE LOT_TABLE SET LOT_NUMBER = 'LOT_NUMBER' WHERE LOT_NUMBER = 'LOT_NUMBER'
						RETURN
				END	
		END
		SELECT @VENDOR_ID = VENDOR_ID FROM VENDOR WHERE V_NAME = @VENDOR_NAME
		IF @VENDOR_ID IS NULL OR @VENDOR_ID <= 0
		BEGIN
				SELECT @VENDOR_ID = VENDOR_ID FROM ALTERNATE_VENDOR_NAMES WHERE ALTERNATE_VENDOR_NAME = @VENDOR_NAME
				IF @VENDOR_ID IS NULL OR @VENDOR_ID <= 0
				BEGIN
					UPDATE LOT_TABLE SET LOT_NUMBER = 'LOT_NUMBER' WHERE LOT_NUMBER = 'LOT_NUMBER'
					RETURN
				END
		END
		SELECT TOP 1 @shippingAddressId =  Address_Id FROM LOCATION WHERE Vendor_Id = @vendor_Id
		select @billingAddressId = address_id from Client_Table

		SELECT @PO_ID = PURCHASEORDER_ID FROM PURCHASEORDER_TABLE WHERE PURCHASEORDER_NO = @PO_NO AND VENDOR_ID = @VENDOR_ID
		IF @PO_ID IS NULL
		BEGIN
				INSERT INTO PurchaseOrder_Table( PurchaseOrder_No, PurchaseOrder_Type, Vendor_Id, PO_Shipping_Address_Id, PO_Billing_address_id, PO_Date, PO_Status)
					VALUES ( @PO_NO, @PO_TYPE, @VENDOR_ID, @shippingAddressId, @billingAddressId , @PO_DATE, @STATUS  )
				SELECT @PO_ID = @@IDENTITY
		END
		ELSE
		BEGIN
				UPDATE PurchaseOrder_Table SET PO_Status = @STATUS WHERE PURCHASEORDER_ID = @PO_ID
		END
		SELECT @PO_LINE_ITEM_ID = POLineItem_Id FROM PurchaseOrder_Line_Item_Table WHERE PURCHASEORDER_ID = @PO_ID AND LineItem_No = @PO_LINEITEM_NO
		IF @PO_LINE_ITEM_ID IS NULL
		BEGIN
				INSERT INTO PurchaseOrder_Line_Item_Table ( PURCHASEORDER_ID, LineItem_No, Processstep_id, ProcessType, [Description], Requested_Date,
											Requested_Qty, Received_Qty, Status, Is_Cancel, Po_Finance_Hold, PO_Operation_Hold, UnitCost)
					VALUES ( @PO_ID,@PO_LINEITEM_NO, @PROCESSSTEP_ID, @PS_TYPE, @PART_NUMBER,@REQ_DATE, @REQ_QTY, 0, @STATUS, 1, 0, 0,@PO_UNIT_COST ) 
		END
		ELSE
		BEGIN
				UPDATE PurchaseOrder_Line_Item_Table SET STATUS = @STATUS, Requested_Qty = @REQ_QTY, UnitCost=@PO_UNIT_COST WHERE POLineItem_Id = @PO_LINE_ITEM_ID
		END

		IF (SELECT COUNT(*) FROM PurchaseOrder_Line_Item_Table WHERE PURCHASEORDER_ID = @PO_ID AND STATUS = 'APPROVED' ) = 0 
		BEGIN
			  UPDATE PurchaseOrder_Table SET PO_Status = 'Closed' WHERE PURCHASEORDER_ID = @PO_ID
		END
		
END